﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Acceso_a_Datos
{
    public class ConexionBD
    {
        private string cadenaConexion;

        public ConexionBD()
        {
            cadenaConexion = ConfigurationManager.ConnectionStrings["cadenaConexion"].ConnectionString;
        }

        public DataTable SelectQuery(string consulta, SqlParameter[] parametrosSQL)
        {
            try
            {
                using (SqlConnection conexion = new SqlConnection())
                {
                    conexion.ConnectionString = cadenaConexion;

                    using (SqlCommand comando = new SqlCommand())
                    {
                        comando.Connection = conexion;
                        comando.CommandText = consulta;
                        if (parametrosSQL != null)
                            comando.Parameters.AddRange(parametrosSQL);
                        comando.CommandType = CommandType.Text;

                        using (SqlDataAdapter adaptador = new SqlDataAdapter())
                        {
                            adaptador.SelectCommand = comando;
                            DataSet dataSet = new DataSet();
                            adaptador.Fill(dataSet);
                            return dataSet.Tables[0];
                        }
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public DataTable SelectQuerySP(string procedimiento, SqlParameter[] parametrosSQL)
        {
            try
            {
                using (SqlConnection conexion = new SqlConnection())
                {
                    conexion.ConnectionString = cadenaConexion;

                    using (SqlCommand comando = new SqlCommand())
                    {
                        comando.Connection = conexion;
                        comando.CommandText = procedimiento;
                        if (parametrosSQL != null)
                            comando.Parameters.AddRange(parametrosSQL);
                        comando.CommandType = CommandType.StoredProcedure;

                        using (SqlDataAdapter adaptador = new SqlDataAdapter())
                        {
                            adaptador.SelectCommand = comando;
                            DataSet dataSet = new DataSet();
                            adaptador.Fill(dataSet);
                            return dataSet.Tables[0];
                        }
                    }
                    
                }
            }
            catch (Exception e)
            {                
                throw e;
            }
        }

        public void ExecuteNonQuery(string consulta, SqlParameter[] parametrosSQL)
        {
            try
            {
                using (SqlConnection conexion = new SqlConnection())
                {
                    conexion.ConnectionString = cadenaConexion;

                    using (SqlCommand comando = new SqlCommand())
                    {
                        comando.Connection = conexion;
                        comando.CommandText = consulta;
                        comando.Parameters.AddRange(parametrosSQL);
                        comando.CommandType = CommandType.Text;

                        comando.Connection.Open();
                        comando.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void ExecuteNonQuerySP(string procedimiento, SqlParameter[] parametrosSQL)
        {
            try
            {
                using (SqlConnection conexion = new SqlConnection())
                {
                    conexion.ConnectionString = cadenaConexion;

                    using (SqlCommand comando = new SqlCommand())
                    {
                        comando.Connection = conexion;
                        comando.CommandText = procedimiento;
                        comando.Parameters.AddRange(parametrosSQL);
                        comando.CommandType = CommandType.StoredProcedure;

                        comando.Connection.Open();
                        comando.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                
                throw;
            }
        }

        // TEST
        //public void InsertQuery(string consulta, SqlParameter[] parametrosSQL)
        //{
        //    try
        //    {
        //        using (SqlConnection conexion = new SqlConnection())
        //        {
        //            conexion.ConnectionString = cadenaConexión;

        //            using (SqlCommand comando = new SqlCommand())
        //            {
        //                comando.Connection = conexion;
        //                comando.CommandText = consulta;
        //                comando.Parameters.AddRange(parametrosSQL);
        //                comando.CommandType = CommandType.Text;

        //                using (SqlDataAdapter adaptador = new SqlDataAdapter())
        //                {
        //                    adaptador.InsertCommand = comando;
        //                    adaptador.InsertCommand.ExecuteNonQuery();
        //                }
        //            }
        //        }
        //    }
        //    catch (Exception e)
        //    {                
        //        throw;
        //    }
        //}

        //public void UpdateQuery(string consulta, SqlParameter[] parametrosSQL)
        //{
        //    try
        //    {
        //        using (SqlConnection conexion = new SqlConnection())
        //        {
        //            conexion.ConnectionString = cadenaConexión;

        //            using (SqlCommand comando = new SqlCommand())
        //            {
        //                comando.Connection = conexion;
        //                comando.CommandText = consulta;
        //                comando.Parameters.AddRange(parametrosSQL);
        //                comando.CommandType = CommandType.Text;

        //                using (SqlDataAdapter adaptador = new SqlDataAdapter())
        //                {
        //                    adaptador.UpdateCommand = comando;
        //                    adaptador.UpdateCommand.ExecuteNonQuery();
        //                }
        //            }
        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        throw;
        //    }
        //}
    }     
}
